package cn.ekuma.epos.datalogic.define.dao.erp;

import java.util.Date;
import java.util.List;

import com.openbravo.bean.erp.viewbean.FrontProductAnalysisInfo;
import com.openbravo.data.basic.BasicException;
import com.openbravo.data.loader.DataParams;
import com.openbravo.data.loader.DataRead;
import com.openbravo.data.loader.Datas;
import com.openbravo.data.loader.I_Session;
import com.openbravo.data.loader.PreparedSentence;
import com.openbravo.data.loader.QBFBuilder;
import com.openbravo.data.loader.SerializerWriteBasic;
import com.openbravo.data.loader.SerializerWriteParams;
import com.openbravo.data.loader.StaticSentence;

import cn.ekuma.data.dao.ViewDAO;

public class FrontProductAnalysisInfoDAO extends ViewDAO<FrontProductAnalysisInfo>{

	public FrontProductAnalysisInfoDAO(I_Session s) {
		super(s);
	}

	@Override
	public Class getSuportClass() {
		return FrontProductAnalysisInfo.class;
	}

	@Override
	public FrontProductAnalysisInfo readValues(DataRead dr, FrontProductAnalysisInfo obj) throws BasicException {
		if(obj==null)
			obj=new FrontProductAnalysisInfo();
		obj.setPosType(dr.getInt(1));
		if(dr.getString(2)==null){
			obj.setM_sName("未登记商品");
			obj.setM_bCom(false);
			obj.setM_bScale(false);
			//obj.setM_dPriceBuy();
			//obj.setM_dPriceSell(dr.getDouble(9));
			//obj.setM_dCustomerPrice(dr.getDouble(10));
			//obj.setCategory(dr.getString(11));
			//obj.setAttributeset(dr.getString(12));
			//obj.setM_origin(dr.getString(13));
			//obj.setM_quantity(dr.getDouble(14));
			//obj.setM_sSimilarCode(dr.getString(15));
			//obj.setM_sManufacturer(dr.getString(16));
			//obj.setM_iDurability(dr.getInt(17));
			//obj.setMultiply(dr.getDouble(18));
			//obj.setMaximum(dr.getDouble(19));
			obj.setState(1);
			obj.setMult(dr.getDouble(22));
			obj.setTotal(dr.getDouble(23));
			obj.setCost(dr.getDouble(24));
		}else{
		obj.setM_ID(dr.getString(2));
		obj.setM_sRef(dr.getString(3));
		obj.setM_sCode(dr.getString(4));
		obj.setM_sName(dr.getString(5));
		obj.setM_bCom(dr.getBoolean(6));
		obj.setM_bScale(dr.getBoolean(7));
		obj.setM_dPriceBuy(dr.getDouble(8));
		obj.setM_dPriceSell(dr.getDouble(9));
		obj.setM_dCustomerPrice(dr.getDouble(10));
		obj.setCategory(dr.getString(11));
		obj.setAttributeset(dr.getString(12));
		obj.setM_origin(dr.getString(13));
		obj.setM_unit(dr.getString(14));
		obj.setM_quantity(dr.getDouble(15));
		obj.setM_sSimilarCode(dr.getString(16));
		obj.setM_sManufacturer(dr.getString(17));
		obj.setM_iDurability(dr.getInt(18));
		obj.setMultiply(dr.getDouble(19)==null?0:dr.getDouble(19));
		obj.setMaximum(dr.getDouble(20)==null?0:dr.getDouble(20));
		obj.setState(dr.getInt(21)==null?0:dr.getInt(21));
		obj.setMult(dr.getDouble(22));
		obj.setTotal(dr.getDouble(23));
		obj.setCost(dr.getDouble(24));
		}
		return obj;
	}

	 @Override
	    public List<FrontProductAnalysisInfo> list(Object filter) throws BasicException {
	                return new StaticSentence(s, new QBFBuilder(
	                "SELECT T.TICKETTYPE,L.PRODUCT, P.REFERENCE, P.CODE, P.NAME, P.ISCOM, P.ISSCALE, P.PRICEBUY, P.PRICESELL, P.CUSTOMERPRICESELL,C.NAME,P.ATTRIBUTESET_ID,P.ORIGIN,P.UNIT,P.QUANTITY,P.SIMILARCODE,P.MANUFACTUR,P.DURABILITY,SL.STOCKSECURITY,SL.STOCKMAXIMUM,PL.STATE,SUM(L.UNITS),SUM(L.UNITS*L.PRICE*L.PRICERATE),SUM(L.UNITS*L.CONSULTCOST) "
	                + "FROM TICKETLINES L "
	                + "LEFT JOIN TICKETS T ON L.TICKET = T.ID "
	                + "LEFT JOIN PRODUCTS P ON L.PRODUCT = P.ID "
	                + "LEFT OUTER JOIN STOCKCURRENT S ON L.PRODUCT=S.PRODUCT "
	                + "LEFT OUTER JOIN CATEGORIES C ON P.CATEGORY = C.ID "
	                + "LEFT OUTER JOIN STOCKLEVEL SL ON S.PRODUCT = SL.PRODUCT  "
	                + "LEFT OUTER JOIN PRODUCT_LOCATION_STATE PL ON S.PRODUCT = PL.PRODUCT_ID AND S.LOCATION=PL.LOCATION_ID  "
	                + "LEFT OUTER JOIN OPERATIONPRODUCT OP ON S.PRODUCT = OP.PRODUCT  "
	                + "LEFT OUTER JOIN OPERATIONPRODUCTCATEGORIES OPC ON OP.OPERATIONCATEGORIE = OPC.ID  "
	                + "LEFT OUTER JOIN LOCATION_LAYOUT_CURRENT LLC ON S.PRODUCT = LLC.PRODUCT   "
	                + "WHERE ?(QBF_FILTER) "
	                + "GROUP BY T.TICKETTYPE,L.PRODUCT, P.REFERENCE, P.CODE, P.NAME, P.ISCOM, P.ISSCALE, P.PRICEBUY, P.PRICESELL, P.CUSTOMERPRICESELL,C.NAME,P.ATTRIBUTESET_ID,P.ORIGIN,P.UNIT,P.QUANTITY,P.SIMILARCODE,P.MANUFACTUR,P.DURABILITY,SL.STOCKSECURITY,SL.STOCKMAXIMUM,PL.STATE ",
	                new String[]{"T.CURDATE", "T.CURDATE","T.LOCATIONREF", "L.PRODUCT", "P.CATEGORY","OPC.ID","LLC.LOCATIONLAYOUT"}), 
new SerializerWriteBasic(new Datas[]{Datas.OBJECT, Datas.TIMESTAMP, Datas.OBJECT, Datas.TIMESTAMP,Datas.OBJECT, Datas.STRING, Datas.OBJECT, Datas.OBJECTS, Datas.OBJECT, Datas.OBJECTS, Datas.OBJECT, Datas.STRING, Datas.OBJECT, Datas.STRING}), this).list(filter);
	    }
	    
	    public List<FrontProductAnalysisInfo> listFrontProductAnalysisByCustomer(final String location,final String customer,Date startTime,Date endTime) throws BasicException {
	    	String timeFilter=QBFBuilder.getSQLFilter("T.CURDATE", startTime, endTime);
	    	return new PreparedSentence(s, 
		                "SELECT T.TICKETTYPE,L.PRODUCT, P.REFERENCE, P.CODE, P.NAME, P.ISCOM, P.ISSCALE, P.PRICEBUY, P.PRICESELL, P.CUSTOMERPRICESELL,C.NAME,P.ATTRIBUTESET_ID,P.ORIGIN,P.UNIT,P.QUANTITY,P.SIMILARCODE,P.MANUFACTUR,P.DURABILITY,SL.STOCKSECURITY,SL.STOCKMAXIMUM,PL.STATE,SUM(L.UNITS),SUM(L.UNITS*L.PRICE*L.PRICERATE),SUM(L.UNITS*L.CONSULTCOST) "
		                + "FROM TICKETLINES L "
		                + "LEFT JOIN TICKETS T ON L.TICKET = T.ID "
		                + "LEFT JOIN PRODUCTS P ON L.PRODUCT = P.ID "
		                + "LEFT OUTER JOIN STOCKCURRENT S ON L.PRODUCT=S.PRODUCT "
		                + "LEFT OUTER JOIN CATEGORIES C ON P.CATEGORY = C.ID "
		                + "LEFT OUTER JOIN STOCKLEVEL SL ON S.PRODUCT = SL.PRODUCT  "
		                + "LEFT OUTER JOIN PRODUCT_LOCATION_STATE PL ON S.PRODUCT = PL.PRODUCT_ID AND S.LOCATION=PL.LOCATION_ID  "
		                + "WHERE S.LOCATION=? AND S.PRODUCT IN(SELECT PRODUCT_ID FROM CUSTOMER_PRODUCT WHERE CUSTOMER_ID=?) AND "+timeFilter
		                + "GROUP BY T.TICKETTYPE,L.PRODUCT, P.REFERENCE, P.CODE, P.NAME, P.ISCOM, P.ISSCALE, P.PRICEBUY, P.PRICESELL, P.CUSTOMERPRICESELL,C.NAME,P.ATTRIBUTESET_ID,P.ORIGIN,P.UNIT,P.QUANTITY,P.SIMILARCODE,P.MANUFACTUR,P.DURABILITY,SL.STOCKSECURITY,SL.STOCKMAXIMUM,PL.STATE ", SerializerWriteParams.INSTANCE, this).list(new DataParams() {
		    				public void writeValues() throws BasicException {
		    					setString(1, location);
		    					setString(2, customer);
		    				}
		    			});
	    }
	    
	    public List<FrontProductAnalysisInfo> listFrontProductAnalysisByProductSimilarCode(final String location,final String productId,Date startTime,Date endTime) throws BasicException {
	    	String timeFilter=QBFBuilder.getSQLFilter("T.CURDATE", startTime, endTime);
	    	return new PreparedSentence(s, 
		                "SELECT T.TICKETTYPE,L.PRODUCT, P.REFERENCE, P.CODE, P.NAME, P.ISCOM, P.ISSCALE, P.PRICEBUY, P.PRICESELL, P.CUSTOMERPRICESELL,C.NAME,P.ATTRIBUTESET_ID,P.ORIGIN,P.UNIT,P.QUANTITY,P.SIMILARCODE,P.MANUFACTUR,P.DURABILITY,SL.STOCKSECURITY,SL.STOCKMAXIMUM,PL.STATE,SUM(L.UNITS),SUM(L.PRICE*L.PRICERATE),SUM(L.CONSULTCOST) "
		                + "FROM TICKETLINES L "
		                + "LEFT JOIN TICKETS T ON L.TICKET = T.ID "
		                + "LEFT JOIN PRODUCTS P ON L.PRODUCT = P.ID "
		                + "LEFT OUTER JOIN STOCKCURRENT S ON L.PRODUCT=S.PRODUCT "
		                + "LEFT OUTER JOIN CATEGORIES C ON P.CATEGORY = C.ID "
		                + "LEFT OUTER JOIN STOCKLEVEL SL ON S.PRODUCT = SL.PRODUCT  "
		                + "LEFT OUTER JOIN PRODUCT_LOCATION_STATE PL ON S.PRODUCT = PL.PRODUCT_ID AND S.LOCATION=PL.LOCATION_ID  "
		                + "WHERE S.LOCATION=? AND P.SIMILARCODE IS NOT NULL AND P.SIMILARCODE!='' AND P.SIMILARCODE IN(SELECT SIMILARCODE FROM PRODUCTS WHERE ID = ?)    AND "+timeFilter
		                + "GROUP BY T.TICKETTYPE,L.PRODUCT, P.REFERENCE, P.CODE, P.NAME, P.ISCOM, P.ISSCALE, P.PRICEBUY, P.PRICESELL, P.CUSTOMERPRICESELL,C.NAME,P.ATTRIBUTESET_ID,P.ORIGIN,P.UNIT,P.QUANTITY,P.SIMILARCODE,P.MANUFACTUR,P.DURABILITY,SL.STOCKSECURITY,SL.STOCKMAXIMUM,PL.STATE ", SerializerWriteParams.INSTANCE, this).list(new DataParams() {
		    				public void writeValues() throws BasicException {
		    					setString(1, location);
		    					setString(2, productId);
		    				}
		    			});
	    }
	    
	    
	    public List<FrontProductAnalysisInfo> listFrontProductAnalysisByNewProducts(final String location,Date newProductStartTime,Date newProductEndTime,Date startTime,Date endTime) throws BasicException {
	    	String timeFilter=QBFBuilder.getSQLFilter("T.CURDATE", startTime, endTime);
	    	String newProductTimeFilter=QBFBuilder.getSQLFilter("P.CURDATE", newProductStartTime, newProductEndTime);
	    	return new PreparedSentence(s, 
		                "SELECT T.TICKETTYPE,L.PRODUCT, P.REFERENCE, P.CODE, P.NAME, P.ISCOM, P.ISSCALE, P.PRICEBUY, P.PRICESELL, P.CUSTOMERPRICESELL,C.NAME,P.ATTRIBUTESET_ID,P.ORIGIN,P.UNIT,P.QUANTITY,P.SIMILARCODE,P.MANUFACTUR,P.DURABILITY,SL.STOCKSECURITY,SL.STOCKMAXIMUM,PL.STATE,SUM(L.UNITS),SUM(L.PRICE*L.PRICERATE),SUM(L.CONSULTCOST) "
		                + "FROM TICKETLINES L "
		                + "LEFT JOIN TICKETS T ON L.TICKET = T.ID "
		                + "LEFT JOIN PRODUCTS P ON L.PRODUCT = P.ID "
		                + "LEFT OUTER JOIN STOCKCURRENT S ON L.PRODUCT=S.PRODUCT "
		                + "LEFT OUTER JOIN CATEGORIES C ON P.CATEGORY = C.ID "
		                + "LEFT OUTER JOIN STOCKLEVEL SL ON S.PRODUCT = SL.PRODUCT  "
		                + "LEFT OUTER JOIN PRODUCT_LOCATION_STATE PL ON S.PRODUCT = PL.PRODUCT_ID AND S.LOCATION=PL.LOCATION_ID  "
		                + "WHERE S.LOCATION=?  AND "+timeFilter+" AND "+newProductTimeFilter
		                + "GROUP BY T.TICKETTYPE,L.PRODUCT, P.REFERENCE, P.CODE, P.NAME, P.ISCOM, P.ISSCALE, P.PRICEBUY, P.PRICESELL, P.CUSTOMERPRICESELL,C.NAME,P.ATTRIBUTESET_ID,P.ORIGIN,P.UNIT,P.QUANTITY,P.SIMILARCODE,P.MANUFACTUR,P.DURABILITY,SL.STOCKSECURITY,SL.STOCKMAXIMUM,PL.STATE ", SerializerWriteParams.INSTANCE, this).list(new DataParams() {
		    				public void writeValues() throws BasicException {
		    					setString(1, location);
		    				}
		    			});
	    }
	    
	    
	    
	    
	    
	  /**  
	    public List<FrontProductAnalysisInfo> list(QBFParameters paras) throws BasicException {
	        return new StaticSentence(s, new QBFBuilder(
	        "SELECT S.PRODUCT, P.REFERENCE, P.CODE, P.NAME, P.ISCOM, P.ISSCALE, P.PRICEBUY, P.PRICESELL, P.CUSTOMERPRICESELL,C.NAME,P.ATTRIBUTESET_ID,P.ORIGIN,P.UNIT,P.QUANTITY,P.SIMILARCODE,P.MANUFACTUR,P.DURABILITY,L.STOCKSECURITY,L.STOCKMAXIMUM,PL.STATE,SUM(S.UNITS) "
	        + "FROM STOCKCURRENT S "
	        + "LEFT JOIN PRODUCTS P ON S.PRODUCT = P.ID "
	        + "LEFT OUTER JOIN CATEGORIES C ON P.CATEGORY = C.ID "
	        + "LEFT OUTER JOIN STOCKLEVEL L ON S.PRODUCT = L.PRODUCT  "
	        + "LEFT OUTER JOIN PRODUCT_LOCATION_STATE PL ON S.PRODUCT = PL.PRODUCT_ID AND S.LOCATION=PL.LOCATION_ID  "
	        + "LEFT OUTER JOIN OPERATIONPRODUCT OP ON S.PRODUCT = OP.PRODUCT  "
	        + "LEFT OUTER JOIN OPERATIONPRODUCTCATEGORIES OPC ON OP.OPERATIONCATEGORIE = OPC.ID  "
	        + "LEFT OUTER JOIN LOCATION_LAYOUT_CURRENT LLC ON S.PRODUCT = LLC.PRODUCT   "
	        + "WHERE ?(QBF_FILTER) "
	        + "GROUP BY S.PRODUCT, P.REFERENCE, P.CODE, P.NAME, P.ISCOM, P.ISSCALE, P.PRICEBUY, P.PRICESELL, P.CUSTOMERPRICESELL,C.NAME,P.ATTRIBUTESET_ID,P.ORIGIN,P.UNIT,P.QUANTITY,P.SIMILARCODE,P.MANUFACTUR,P.DURABILITY,L.STOCKSECURITY,L.STOCKMAXIMUM,PL.STATE "), null, this).list(paras);
	   }
	   
	   */


}
